
<?php
//include_once("../modules/checkA.php");
//include_once("../../config.php");
//?>
<!---->
<!---->

<?
require_once ROOT_PATH_.'/application/PHPExcel/PHPExcel.php';
require_once ROOT_PATH_.'/application/PHPExcel/PHPExcel/Writer/Excel2007.php';
require_once ROOT_PATH_.'/application/PHPExcel/PHPExcel/Writer/Excel5.php';
require_once ROOT_PATH_.'/application/PHPExcel/PHPExcel/IOFactory.php';
?>



<?


date_default_timezone_set('Europe/London'); //设置时间



$inputFileName = ROOT_PATH_.'/uploadfiles/'.$name;

//判断一下，文件是2007还是旧版的excel
if(strstr($name,".")==".xls")
{
    $inputFileType = 'Excel5';
}
else if(strstr($name,".")==".xlsx")
{
    $inputFileType = 'Excel2007';
}
else
{
    echo "请检查文件格式";
}


//$inputFileType = 'Excel5';    //这个是读
//$inputFileType = 'Excel2007';//这个是计xlsx的
//$inputFileName = 'sample.xls';





//echo 'Loading file ',pathinfo($inputFileName,PATHINFO_BASENAME),' using IOFactory with a defined reader type of ',$inputFileType,'<br />';
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load($inputFileName);
/*
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow(); //取得总行数
$highestColumn = $sheet->getHighestColumn(); //取得总列
*/
$objWorksheet = $objPHPExcel->getActiveSheet();//取得总行数
$highestRow = $objWorksheet->getHighestRow();//取得总列数

echo 'highestRow='.$highestRow;
//echo "<br>";
$highestColumn = $objWorksheet->getHighestColumn();
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);//总列数
echo 'highestColumnIndex='.$highestColumnIndex;
//echo "<br />";



$headtitle=array();
$result=array();

for ($row = 0;$row <= $highestRow;$row++)
{
    $strs=array();
    //注意highestColumnIndex的列数索引从0开始
    for ($col = 0;$col < $highestColumnIndex;$col++)
    {
        $strs[$col] =$objWorksheet->getCellByColumnAndRow($col, $row)->getValue();

        $result[$row][$col]=$strs[$col];
    }


}





 //已经拥有数组，然后按照这个数组，去批量插入数据。








$authur=$_SESSION['username'];



//设置时间用
$d = 25569;
$t = 24 * 60 * 60;


$date=date("Y-m-d h:m:s");

//如果第一行有标题的话，i就设置从1算起。




$employee_fulllist  = select_lines_all_items($table_Prefix . "employee", "nid desc", "", "0");
$employee_list      = transform_2darray_in_1darray_with_new_key($employee_fulllist, "name", "nid");


$member_list        = two_d_array_item_in_one_d_array($result, "0");
$member_list_sqlstr = str_replace(",序号,", "", implode(",", $member_list));

$member_fulllist    = select_lines_all_items($table_Prefix . "member", "nid desc", "nid in(" . $member_list_sqlstr . ")", "0");

//$member_list_2d     = transform_2darray_in_1darray_with_new_key($member_fulllist, "nid", "nid");




$equipment_fulllist  = select_lines_all_items($table_Prefix . "equipment", "nid desc", "", "0");
$equipment_list      = transform_2darray_in_1darray_with_new_key($equipment_fulllist, "name", "nid");


//print_r2($member_list_2d);




for($i=2;$i<count($result);$i++)
{



    $result[$i][5]=gmdate('Y-m-d H:i:s', ($result[$i][5] - $d) * $t);


    //0序号,1科目名称,2姓名的nid,3教练,4身份证号码,5考试日期,6状态,7考试车型,8车牌


  //导入的人的名称
  $authur=$_SESSION['username'];


    if($result[$i][0]){
  $values="('$date','".$result[$i][0]."','".$examname."','".$result[$i][5]."','".$result[$i][6]."','".$employee_list[$result[$i][3]]."','".$result[$i][7]."','".$equipment_list[$result[$i][8]]."','".$authur."'),".$values;







    //如果表格中，含有非对应科目的科目名字，则整个写入终止
    if($examname!=$result[$i][1])
    {

        $inserttag="block";
    }

 

    }



//同时处理，科目四的话，通过的话，就把学员结业。

//2姓名的nid,5考试日期,6状态
    if($result[$i][6]=="通过")
    {
        $graduate_member_nid[]       = $result[$i][0];
        $graduate_member[$result[$i][0]] = $result[$i][5];
    }





}




$values=rtrim($values, ',');












//设置需要搜索的表格的前缀后缀
$table_Suffix = "member";
$table = $table_Prefix . $table_Suffix;
//设置需要搜索的表格的前缀后缀


$ids = implode(',', $graduate_member_nid);


$sql_member = "UPDATE $table SET graduatedate = CASE nid ";


foreach ($graduate_member as $nid => $ordinal) {
    $sql_member .= sprintf("WHEN %d THEN '%s' ", $nid, $ordinal);
}

$sql_member .= "END , graduate = '结业' WHERE nid IN ($ids)";


echo $query = mysql_query($sql_member) or die ("error:".mysql_error());








//设置需要搜索的表格的前缀后缀
$table_Suffix = "exam";
$table = $table_Prefix . $table_Suffix;
//设置需要搜索的表格的前缀后缀





$sql = "INSERT INTO $table (
date,
masterid,
name,
applydate,
status,
coachid,
cartype,
equipmentid,
authur
)

value $values ";









if( $inserttag!="block"){
   $query = mysql_query($sql) or die ("error:".mysql_error());
}
else{
    echo "写入失败,请检查科目名称";
}

mysql_close($con);





?>